import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Random;

import common.SQLCall;

public class PopulateQuestions
{
	Connection conn;
	ArrayList<Integer> users;
	Random rnd = new Random();

	public static void main(String[] args)
	{
		PopulateQuestions pop = new PopulateQuestions();
		try
		{
			pop.connect();

			pop.setup();
			pop.generate();

			pop.close();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}

	}

	public PopulateQuestions()
	{
	}

	public PopulateQuestions(Connection c)
	{
		conn = c;
	}

	public void connect() throws SQLException
	{
		DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
		conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@146.187.134.17:1542:oracle10g", "cscd494",
				"R3tral435");

	}

	public void close() throws SQLException
	{
		conn.close();
	}

	public void setup()
	{
		users = SQLCall.getApeUsers(conn);
	}

	public void generate() throws SQLException
	{
		ProblemIterator foo = new ProblemIterator("sourcepool");
		int qContentID = 0;
		int questionID = 0;
		while (foo.hasNext())
		{
			ApeProblem ap = foo.next();
			qContentID = fillQuestionContent(ap);
			questionID = fillQuestionTable(qContentID, ap);
			fillSource(questionID, ap);
		}
	}

	public int fillQuestionContent(ApeProblem ap) throws SQLException
	{
		int qContentID = SQLCall.getNextSeq(conn, "question_content_s1");

		try
		{
			PreparedStatement pst = conn
					.prepareStatement("INSERT INTO QUESTION_CONTENT (question_content_id, "
							+ "question_title, author_id, points) "
							+ "VALUES(?,?,?,?)");
			int var = 1;
			pst.setInt(var++, qContentID);// question_content_id

			pst.setString(var++, ap.title);// question_title
			System.out.println(ap.title);

			pst.setInt(var++, users.get(rnd.nextInt(users.size())));// author_id

			pst.setInt(var++, ap.getPoints(conn));// points

			pst.execute();
			pst.close();

		} catch (Exception sqlError)
		{
			sqlError.printStackTrace();
		}

		return qContentID;

	}

	public int fillQuestionTable(int qContentID, ApeProblem ap) throws SQLException
	{
		int questionID = SQLCall.getNextSeq(conn, "question_s1");
		try
		{
			PreparedStatement pst = conn
					.prepareStatement("INSERT INTO QUESTION (question_id, q_version, "
							+ "question_type_code, question_content_id, "
							+ "date_modified) VALUES(?, 0, ?, ?, ?)");

			int var = 1;
			pst.setInt(var++, questionID);// question_id
			pst.setInt(var++, ap.getCategory());
			pst.setInt(var++, qContentID);// question_content_id
			pst.setDate(var++, ap.getDateCreated());
			pst.execute();
			pst.close();
		} catch (SQLException sqlError)
		{
			sqlError.printStackTrace();
		}

		return questionID;
	}

	public void fillSource(int questionID, ApeProblem ap)
	{
		try
		{

			PreparedStatement pst = null;
			int sourceFileID = 0;
			SourceIterator bar = ap.getSource();
			FileInputStream upStream;

			pst = conn.prepareStatement("INSERT INTO SOURCE_FILE "
					+ "(question_id, q_version, source_file_name, "
					+ "source_file, source_file_id, kin) "
					+ "VALUES(?, 0, ?, ?, ?, ?)");

			pst.setInt(1, questionID);// question_id

			for (int i = 0; i < ap.file.length; i++)
			{
				if (ap.file[i] != null)
				{
					upStream = new FileInputStream(ap.file[i]);
					sourceFileID = SQLCall.getNextSeq(conn, "source_file_s1");
					pst.setString(2, ap.file[i].getName());// source_file_name
					pst.setBinaryStream(3, upStream, (int) ap.file[i].length());
					pst.setInt(4, sourceFileID);// source_file_id
					pst.setInt(5, KinID.ApeFileID(i));// kin number
					pst.execute();
				}
			}

			pst.close();

			pst = conn.prepareStatement("INSERT INTO SOURCE_FILE "
					+ "(question_id, q_version, source_file_name, "
					+ "source_file, source_file_id, kin) "
					+ "VALUES(?, 0, ?, ?, ?, ?)");

			pst.setInt(5, KinID.source_file());// Kin ID number
			while (bar.hasNext())
			{
				sourceFileID = SQLCall.getNextSeq(conn, "source_file_s1");
				File sourceFile = bar.next();
				upStream = new FileInputStream(sourceFile);

				pst.setInt(1, questionID);// question_id
				pst.setString(2, sourceFile.getName());// source_file_name

				pst.setBinaryStream(3, upStream, (int) sourceFile.length());// source_file
				pst.setInt(4, sourceFileID);// source_file_id
				pst.execute();
			}
			pst.close();
		} catch (Exception sqlError)
		{
			sqlError.printStackTrace();
		}
	}
}
